-- SP_FORECAST_WATER_FALL 10, 9

ALTER PROC SP_FORECAST_WATER_FALL ( @PROJECT_ID INT, @PRODUCT_ID INT )
AS
BEGIN
	DECLARE @RESULT_TABLE TABLE  ( INDEX_ID INT, MONTH_STR VARCHAR(20), MONTH1_QTY INT, MONTH2_QTY INT, MONTH3_QTY INT, MONTH4_QTY INT, MONTH5_QTY INT, MONTH6_QTY INT, MONTH7_QTY INT, MONTH8_QTY INT, MONTH9_QTY INT, MONTH10_QTY INT, MONTH11_QTY INT, MONTH12_QTY INT, MONTH13_QTY INT, MONTH14_QTY INT, MONTH15_QTY INT, MONTH16_QTY INT, MONTH17_QTY INT, MONTH18_QTY INT  )
	DECLARE @TEMP_TABLE TABLE ( COL_IDX INT, MONTH INT, YEAR INT )
	DECLARE @CURRENT_MONTH INT
	DECLARE @CURRENT_YEAR INT
	DECLARE @DATA_MONTH INT
	DECLARE @DATA_YEAR INT
	DECLARE @INDEX INT
	DECLARE @COL_IDX INT
	DECLARE @CUSTOMER_ID INT
	DECLARE @QTY INT
	DECLARE @MONTH INT
	DECLARE @YEAR INT
	DECLARE @START_DATE DATETIME
	DECLARE @END_DATE DATETIME
	DECLARE @BACKLOG_PRODUCT_ID INT	
	DECLARE @IS_CHIPSET VARCHAR(50)
	DECLARE @DATA_CURSOR CURSOR

	SET @CURRENT_MONTH	= MONTH(GETDATE())
	SET @CURRENT_YEAR	= YEAR(GETDATE())

	IF ( @CURRENT_MONTH - 6 <= 0  )
	BEGIN
		SET @DATA_MONTH = (@CURRENT_MONTH - 6)+12
		SET @DATA_YEAR	= @CURRENT_YEAR - 1
	END
	ELSE
	BEGIN
		SET @DATA_MONTH = @CURRENT_MONTH - 6
		SET @DATA_YEAR	= @CURRENT_YEAR 
	END

	SELECT @IS_CHIPSET = Parameter_Value FROM PROCESS_PARAMETER WHERE ProcessStep_ID = ( SELECT PROCESSSTEP_ID FROM PRODUCT WHERE PRODUCT_ID = @PRODUCT_ID )
	AND Parameter_Key = 'IsChipset'
	
	IF @IS_CHIPSET IS NOT NULL AND @IS_CHIPSET = 'true'
	BEGIN
		SELECT TOP 1 @BACKLOG_PRODUCT_ID = P.Product_ID FROM Assembly_BOM_Setup_Table ABST, Product P WHERE ABST.BOM_ProcessStep_ID = P.ProcessStep_ID AND Assembly_ProcessStep_ID = ( SELECT PROCESSSTEP_ID FROM PRODUCT WHERE PRODUCT_ID = @PRODUCT_ID ) AND ABST.IS_PRIMARY_PART = 0
		IF @BACKLOG_PRODUCT_ID IS NULL
		BEGIN 
			SET @BACKLOG_PRODUCT_ID = @PRODUCT_ID
		END
	END
	ELSE
	BEGIN
		SET @BACKLOG_PRODUCT_ID = @PRODUCT_ID
	END

	SET @COL_IDX = 1
	SELECT @CUSTOMER_ID = CUSTID FROM PROJSETUP WHERE PROJID = @PROJECT_ID

	WHILE ( @COL_IDX < 19 )	
	BEGIN
		
		SET @START_DATE = CONVERT( VARCHAR, @DATA_MONTH )+'/01/'+ CONVERT( VARCHAR, @DATA_YEAR )
		--SET @END_DATE	= DATEADD( mi, -1 , DATEADD( m, 1, CONVERT(DATETIME, @START_DATE ) ) )
				
		INSERT INTO @TEMP_TABLE ( COL_IDX, MONTH, YEAR ) VALUES ( @COL_IDX, MONTH( @START_DATE )-1, YEAR( @START_DATE ) )

		IF ( @DATA_MONTH + 1 > 12  )
		BEGIN
			SET @DATA_MONTH = 1
			SET @DATA_YEAR	= @DATA_YEAR + 1
		END
		ELSE
		BEGIN
			SET @DATA_MONTH = @DATA_MONTH + 1
			SET @DATA_YEAR	= @DATA_YEAR 
		END
		
		SET @COL_IDX = @COL_IDX + 1
	END

	--SELECT * FROM @TEMP_TABLE

	IF ( @CURRENT_MONTH - 6 <= 0  )
	BEGIN
		SET @DATA_MONTH = (@CURRENT_MONTH - 6)+12
		SET @DATA_YEAR	= @CURRENT_YEAR - 1
	END
	ELSE
	BEGIN
		SET @DATA_MONTH = @CURRENT_MONTH - 6
		SET @DATA_YEAR	= @CURRENT_YEAR 
	END
	SET @INDEX = 0

	INSERT INTO @RESULT_TABLE ( INDEX_ID, MONTH_STR ) VALUES ( 8, 'Actual Shipments' )

	WHILE ( @INDEX < 7 )	
	BEGIN
		
		SET @START_DATE = CONVERT( VARCHAR, @DATA_MONTH )+'/01/'+ CONVERT( VARCHAR, @DATA_YEAR )
		SET @END_DATE	= DATEADD( mi, -1 , DATEADD( m, 1, CONVERT(DATETIME, @START_DATE ) ) )
		PRINT 'Start Date ' + CONVERT( VARCHAR, @START_DATE )
		PRINT 'End Date ' + CONVERT(VARCHAR, @END_DATE )
		PRINT CONVERT(DATETIME, CONVERT( VARCHAR, @DATA_MONTH )+'/01/'+ CONVERT( VARCHAR, @DATA_YEAR ) )
		
		INSERT INTO @RESULT_TABLE ( INDEX_ID, MONTH_STR ) VALUES ( @INDEX, CONVERT( VARCHAR,DATENAME( MONTH, @START_DATE ))+ '-'+ CONVERT(VARCHAR, YEAR( @START_DATE ) ) )

		IF( @START_DATE < CONVERT(DATETIME, CONVERT( VARCHAR, @CURRENT_MONTH )+'/01/'+ CONVERT( VARCHAR, @CURRENT_YEAR ) ) )
		BEGIN
			SET @DATA_CURSOR = CURSOR FOR SELECT QTY, MONTH, YEAR FROM FORECAST_SNAPSHOT_TABLE WHERE PROJID = @PROJECT_ID AND ProdId = @PRODUCT_ID AND TIMESTAMP >= @START_DATE AND TIMESTAMP < @END_DATE	
		END
		ELSE
		BEGIN
			SET @DATA_CURSOR = CURSOR FOR SELECT QTY, MONTH, YEAR FROM FCENTRYMASTER WHERE PROJID = @PROJECT_ID AND ProdId = @PRODUCT_ID
		END

		OPEN @DATA_CURSOR
		FETCH NEXT FROM @DATA_CURSOR INTO @QTY, @MONTH, @YEAR
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SELECT @COL_IDX = COL_IDX FROM @TEMP_TABLE WHERE MONTH = @MONTH AND YEAR = @YEAR
			
			IF @COL_IDX = 1
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH1_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 2
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH2_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 3
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH3_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 4
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH4_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 5
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH5_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 6
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH6_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 7
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH7_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 8
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH8_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 9
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH9_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 10
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH10_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 11
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH11_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 12
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH12_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 13
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH13_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 14
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH14_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 15
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH15_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 16
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH16_QTY = @QTY WHERE INDEX_ID = @INDEX
			END	
			ELSE IF @COL_IDX = 17
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH17_QTY = @QTY WHERE INDEX_ID = @INDEX
			END
			ELSE IF @COL_IDX = 18
			BEGIN
				UPDATE @RESULT_TABLE SET MONTH18_QTY = @QTY WHERE INDEX_ID = @INDEX
			END		
			FETCH NEXT FROM @DATA_CURSOR INTO @QTY, @MONTH, @YEAR	
		END
		CLOSE @DATA_CURSOR
		DEALLOCATE @DATA_CURSOR

		-- UPDATE SHIPMENT INFORMATION.
		IF @INDEX = 0
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH1_QTY = (SELECT SUM(CSLIT_Qty_SHIPPED) FROM Customer_Shipping_Line_Item_Table CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO
			WHERE CSLIT.LineItem_ID = COOL.LineItem_ID AND COOL.SALESORDER_ID = SO.SALESORDER_ID AND CUSTOMER_ID = @CUSTOMER_ID AND COOL.Product_ID = @BACKLOG_PRODUCT_ID AND 
			CSLIT_SHIPPED_DATE >= @START_DATE AND  CSLIT_SHIPPED_DATE <= @END_DATE
			AND CSLIT.CSLIT_STATUS = 'SHIPPED') WHERE INDEX_ID = 8
		END	
		ELSE IF @INDEX = 1
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH2_QTY = (SELECT SUM(CSLIT_Qty_SHIPPED) FROM Customer_Shipping_Line_Item_Table CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO
			WHERE CSLIT.LineItem_ID = COOL.LineItem_ID AND COOL.SALESORDER_ID = SO.SALESORDER_ID AND CUSTOMER_ID = @CUSTOMER_ID AND COOL.Product_ID = @BACKLOG_PRODUCT_ID AND 
			CSLIT_SHIPPED_DATE >= @START_DATE AND  CSLIT_SHIPPED_DATE <= @END_DATE
			AND CSLIT.CSLIT_STATUS = 'SHIPPED') WHERE INDEX_ID = 8
		END
		ELSE IF @INDEX = 2
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH3_QTY = (SELECT SUM(CSLIT_Qty_SHIPPED) FROM Customer_Shipping_Line_Item_Table CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO
			WHERE CSLIT.LineItem_ID = COOL.LineItem_ID AND COOL.SALESORDER_ID = SO.SALESORDER_ID AND CUSTOMER_ID = @CUSTOMER_ID AND COOL.Product_ID = @BACKLOG_PRODUCT_ID AND 
			CSLIT_SHIPPED_DATE >= @START_DATE AND  CSLIT_SHIPPED_DATE <= @END_DATE
			AND CSLIT.CSLIT_STATUS = 'SHIPPED') WHERE INDEX_ID = 8
		END
		ELSE IF @INDEX = 3
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH4_QTY = (SELECT SUM(CSLIT_Qty_SHIPPED) FROM Customer_Shipping_Line_Item_Table CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO
			WHERE CSLIT.LineItem_ID = COOL.LineItem_ID AND COOL.SALESORDER_ID = SO.SALESORDER_ID AND CUSTOMER_ID = @CUSTOMER_ID AND COOL.Product_ID = @BACKLOG_PRODUCT_ID AND 
			CSLIT_SHIPPED_DATE >= @START_DATE AND  CSLIT_SHIPPED_DATE <= @END_DATE
			AND CSLIT.CSLIT_STATUS = 'SHIPPED') WHERE INDEX_ID = 8
		END
		ELSE IF @INDEX = 4
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH5_QTY = (SELECT SUM(CSLIT_Qty_SHIPPED) FROM Customer_Shipping_Line_Item_Table CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO
			WHERE CSLIT.LineItem_ID = COOL.LineItem_ID AND COOL.SALESORDER_ID = SO.SALESORDER_ID AND CUSTOMER_ID = @CUSTOMER_ID AND COOL.Product_ID = @BACKLOG_PRODUCT_ID AND 
			CSLIT_SHIPPED_DATE >= @START_DATE AND  CSLIT_SHIPPED_DATE <= @END_DATE
			AND CSLIT.CSLIT_STATUS = 'SHIPPED') WHERE INDEX_ID = 8
		END
		ELSE IF @INDEX = 5
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH6_QTY = (SELECT SUM(CSLIT_Qty_SHIPPED) FROM Customer_Shipping_Line_Item_Table CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO
			WHERE CSLIT.LineItem_ID = COOL.LineItem_ID AND COOL.SALESORDER_ID = SO.SALESORDER_ID AND CUSTOMER_ID = @CUSTOMER_ID AND COOL.Product_ID = @BACKLOG_PRODUCT_ID AND 
			CSLIT_SHIPPED_DATE >= @START_DATE AND  CSLIT_SHIPPED_DATE <= @END_DATE
			AND CSLIT.CSLIT_STATUS = 'SHIPPED') WHERE INDEX_ID = 8
		END
		ELSE IF @INDEX = 6
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH7_QTY = (SELECT SUM(CSLIT_Qty_SHIPPED) FROM Customer_Shipping_Line_Item_Table CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO
			WHERE CSLIT.LineItem_ID = COOL.LineItem_ID AND COOL.SALESORDER_ID = SO.SALESORDER_ID AND CUSTOMER_ID = @CUSTOMER_ID AND COOL.Product_ID = @BACKLOG_PRODUCT_ID AND 
			CSLIT_SHIPPED_DATE >= @START_DATE AND  CSLIT_SHIPPED_DATE <= @END_DATE
			AND CSLIT.CSLIT_STATUS = 'SHIPPED') WHERE INDEX_ID = 8
		END
				
		IF ( @DATA_MONTH + 1 > 12  )
		BEGIN
			SET @DATA_MONTH = 1
			SET @DATA_YEAR	= @DATA_YEAR + 1
		END
		ELSE
		BEGIN
			SET @DATA_MONTH = @DATA_MONTH + 1
			SET @DATA_YEAR	= @DATA_YEAR 
		END
		
		SET @INDEX = @INDEX + 1
	END

	-- UPDATE LAST FORECAST
	INSERT INTO @RESULT_TABLE ( INDEX_ID, MONTH_STR ) VALUES ( 7, 'Last Forecast' )
	UPDATE @RESULT_TABLE SET MONTH1_QTY = (SELECT MONTH1_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 0 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH2_QTY = (SELECT MONTH2_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 1 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH3_QTY = (SELECT MONTH3_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 2 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH4_QTY = (SELECT MONTH4_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 3 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH5_QTY = (SELECT MONTH5_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 4 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH6_QTY = (SELECT MONTH6_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 5 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH7_QTY = (SELECT MONTH7_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 6 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH8_QTY = (SELECT MONTH8_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 6 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH9_QTY = (SELECT MONTH9_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 6 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH10_QTY = (SELECT MONTH10_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 6 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH11_QTY = (SELECT MONTH11_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 6 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH12_QTY = (SELECT MONTH12_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 6 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH13_QTY = (SELECT MONTH13_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 6 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH14_QTY = (SELECT MONTH14_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 6 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH15_QTY = (SELECT MONTH15_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 6 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH16_QTY = (SELECT MONTH16_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 6 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH17_QTY = (SELECT MONTH17_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 6 ) WHERE INDEX_ID = 7
	UPDATE @RESULT_TABLE SET MONTH18_QTY = (SELECT MONTH18_QTY FROM @RESULT_TABLE WHERE INDEX_ID = 6 ) WHERE INDEX_ID = 7
	
	-- UPDATE BACKLOG
	INSERT INTO @RESULT_TABLE ( INDEX_ID, MONTH_STR ) VALUES ( 9, 'Actual Backlog' )

	SET @DATA_CURSOR = CURSOR FOR SELECT COL_IDX, MONTH, YEAR FROM @TEMP_TABLE WHERE COL_IDX > 6 ORDER BY COL_IDX
	OPEN @DATA_CURSOR
	FETCH NEXT FROM @DATA_CURSOR INTO @COL_IDX, @MONTH, @YEAR
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		SET @QTY = 0
		SET @START_DATE = CONVERT( VARCHAR, (@MONTH+1) )+'/01/'+ CONVERT( VARCHAR, @YEAR )
		SET @END_DATE	= DATEADD( mi, -1 , DATEADD( m, 1, CONVERT(DATETIME, @START_DATE ) ) )

		SELECT @QTY = SUM(COOL_QTY) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO WHERE COOL.SALESORDER_ID = SO.SALESORDER_ID AND SO.CUSTOMER_ID = @CUSTOMER_ID AND COOL.Product_ID = @BACKLOG_PRODUCT_ID
		AND COOL_STATUS != 'SHIPPED' AND COOL_REQUESTED_DATE >= @START_DATE AND COOL_REQUESTED_DATE <= @END_DATE

		IF @COL_IDX = 7
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH7_QTY = @QTY WHERE INDEX_ID = 9
		END
		ELSE IF @COL_IDX = 8
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH8_QTY = @QTY WHERE INDEX_ID = 9
		END
		ELSE IF @COL_IDX = 9
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH9_QTY = @QTY WHERE INDEX_ID = 9
		END
		ELSE IF @COL_IDX = 10
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH10_QTY = @QTY WHERE INDEX_ID = 9
		END
		ELSE IF @COL_IDX = 11
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH11_QTY = @QTY WHERE INDEX_ID = 9
		END
		ELSE IF @COL_IDX = 12
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH12_QTY = @QTY WHERE INDEX_ID = 9
		END
		ELSE IF @COL_IDX = 13
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH13_QTY = @QTY WHERE INDEX_ID = 9
		END
		ELSE IF @COL_IDX = 14
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH14_QTY = @QTY WHERE INDEX_ID = 9
		END
		ELSE IF @COL_IDX = 15
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH15_QTY = @QTY WHERE INDEX_ID = 9
		END
		ELSE IF @COL_IDX = 16
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH16_QTY = @QTY WHERE INDEX_ID = 9
		END
		ELSE IF @COL_IDX = 17
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH17_QTY = @QTY WHERE INDEX_ID = 9
		END
		ELSE IF @COL_IDX = 18
		BEGIN
			UPDATE @RESULT_TABLE SET MONTH18_QTY = @QTY WHERE INDEX_ID = 9
		END
		FETCH NEXT FROM @DATA_CURSOR INTO @COL_IDX, @MONTH, @YEAR	
	END
	CLOSE @DATA_CURSOR
	DEALLOCATE @DATA_CURSOR
	
	-- Forecase VS Backlog ** Formula should be reverse as a quick fix multipled by -1. Need to change it when changing the proc
	INSERT INTO @RESULT_TABLE ( INDEX_ID, MONTH_STR ) VALUES ( 10, 'Forecast VS Backlog' )
	UPDATE @RESULT_TABLE SET MONTH1_QTY = (( SELECT ISNULL(MONTH1_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH1_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH1_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH2_QTY = (( SELECT ISNULL(MONTH2_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH2_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH2_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH3_QTY = (( SELECT ISNULL(MONTH3_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH3_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH3_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH4_QTY = (( SELECT ISNULL(MONTH4_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH4_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH4_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH5_QTY = (( SELECT ISNULL(MONTH5_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH5_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH5_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH6_QTY = (( SELECT ISNULL(MONTH6_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH6_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH6_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH7_QTY = (( SELECT ISNULL(MONTH7_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH7_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH7_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH8_QTY = (( SELECT ISNULL(MONTH8_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH8_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH8_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH9_QTY = (( SELECT ISNULL(MONTH9_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH9_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH9_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH10_QTY = (( SELECT ISNULL(MONTH10_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH10_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH10_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH11_QTY = (( SELECT ISNULL(MONTH11_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH11_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH11_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH12_QTY = (( SELECT ISNULL(MONTH12_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH12_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH12_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH13_QTY = (( SELECT ISNULL(MONTH13_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH13_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH13_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH14_QTY = (( SELECT ISNULL(MONTH14_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH14_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH14_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH15_QTY = (( SELECT ISNULL(MONTH15_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH15_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH15_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH16_QTY = (( SELECT ISNULL(MONTH16_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH16_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH16_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH17_QTY = (( SELECT ISNULL(MONTH17_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH17_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH17_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10

	UPDATE @RESULT_TABLE SET MONTH18_QTY = (( SELECT ISNULL(MONTH18_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 7)- ( SELECT ISNULL(MONTH18_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 8 ) -( SELECT ISNULL(MONTH18_QTY, 0) FROM @RESULT_TABLE WHERE INDEX_ID = 9 ) )*-1
	WHERE INDEX_ID = 10


	SELECT * FROM @RESULT_TABLE ORDER BY INDEX_ID
END